package com.google.api.adwords.starterkit.examples.enhancedads;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;

import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.mail.DefaultAuthenticator;
import org.apache.commons.mail.Email;
import org.apache.commons.mail.EmailAttachment;
import org.apache.commons.mail.EmailException;
import org.apache.commons.mail.MultiPartEmail;
import org.apache.commons.mail.SimpleEmail;

import com.google.api.adwords.starterkit.db.structures.AdGroupStructure;
import com.google.api.adwords.starterkit.db.structures.CampaignStructure;
import com.google.api.adwords.starterkit.db.structures.TextAdStructure;
import com.google.api.adwords.starterkit.db.tables.AdGroupTable;
import com.google.api.adwords.starterkit.db.tables.CampaignTable;
import com.google.api.adwords.starterkit.db.tables.TextAdTable;
import com.google.api.adwords.starterkit.examples.enhancedads.db.TextAd2CatId;
import com.google.api.adwords.starterkit.examples.enhancedads.db.TextAd2ProdId;
import com.google.api.adwords.starterkit.examples.enhancedads.settings.AccountSettings;
import com.google.api.adwords.starterkit.examples.enhancedads.settings.EmailSettings;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.starterkit.utils.ReadWriteTextFiles;
import com.google.api.adwords.starterkit.utils.Utils;
import com.google.api.adwords.v201101.cm.AdGroupAdStatus;

public class EmailReport {
  private Connection conn;
  private EmailSettings emailSettings;
  private TextAd2CatId textAd2CatId;
  private TextAd2ProdId textAd2ProdId;
  private TextAdTable textAdTable;
  private AdGroupTable adGroupTable;
  private CampaignTable campaignTable;
  private String content = "";
  private AccountSettings account;

  public EmailReport(Connection conn, AccountSettings account, 
      EmailSettings emailSettings) {
    this.conn = conn;
    this.account = account;
    this.emailSettings = emailSettings;
    textAdTable = new TextAdTable(conn);
    adGroupTable = new AdGroupTable(conn);
    campaignTable = new CampaignTable(conn);
    textAd2CatId = new TextAd2CatId(conn);
    textAd2ProdId = new TextAd2ProdId(conn);    
  }
  
  public void create(HashSet<Integer> textAdsOutOfStock, 
      HashSet<Integer> textAdsOutSchedule, HashSet<Integer> textAdsInStock) {
    createWorkbook(textAdsOutOfStock, textAdsOutSchedule, textAdsInStock);    
  }
  
  public void send() {
    MultiPartEmail email = new MultiPartEmail();
    
    // no host means no email
    if (emailSettings.getHost().length() == 0) {
      return;
    }
    
    email.setHostName(emailSettings.getHost());
    email.setSmtpPort(emailSettings.getPort());
    
    // allow to use emails servers without authentication
    if (emailSettings.getLogin().length() > 0) {
      email.setAuthenticator(new DefaultAuthenticator(emailSettings.getLogin(), 
          emailSettings.getPassword()));
    }
    email.setTLS(emailSettings.getTls());
    
    DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
    Date date = new Date();
    String dataTimeTitle = dateFormat.format(date);
    
    dateFormat = new SimpleDateFormat("yyyyMMdd_HHmm");
    String dataTimeFile = dateFormat.format(date);

    try {
      
      // Create the attachment
      EmailAttachment attachment = new EmailAttachment();
      attachment.setPath(emailSettings.getAttachFile());
      attachment.setDisposition(EmailAttachment.ATTACHMENT);
      attachment.setDescription("EnhancedAds Report - " + account.getLogin() +
          " - " + account.getClientEmail() + " - " + dataTimeTitle);
      attachment.setName("EnhancedAdsReport_" + account.getLogin() +
          "_" + account.getClientEmail() + "_"+ dataTimeFile + ".xls");   
      
      email.setFrom(emailSettings.getFrom());
      email.setSubject(emailSettings.getTitle() + " - " + account.getLogin() +
          " - " + account.getClientEmail());
      email.setMsg(emailSettings.getContent());
      email.attach(attachment);
      
      HashSet<String> destinations = emailSettings.getDestinations();
      Iterator<String> i = destinations.iterator();
      while (i.hasNext()) {
        String destination = i.next();
        email.addTo(destination);
      }
      email.send();        
    } catch (EmailException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
  }
  
  public WritableWorkbook createWorkbook(HashSet<Integer> textAdsOutOfStock, 
      HashSet<Integer> textAdsOutSchedule, HashSet<Integer> textAdsInStock) {

    File attachmentFile = new File(emailSettings.getAttachFile());
    WritableWorkbook workbook = null;
    try {
      workbook = Workbook.createWorkbook(attachmentFile);

      HashSet<TextAdStructure> textAdsStructureInStock 
          = textAdTable.getList(textAdsInStock);
      createSheet(workbook, "ACTIVATED", 0, 
          textAdsStructureInStock, AdGroupAdStatus.PAUSED);

      HashSet<TextAdStructure> textAdsStructureOutOfStock 
          = textAdTable.getList(textAdsOutOfStock);
      createSheet(workbook, "PAUSED", 1, 
          textAdsStructureOutOfStock, AdGroupAdStatus.ENABLED);

      HashSet<TextAdStructure> textAdsStructureOutOfSchedule 
          = textAdTable.getList(textAdsOutSchedule);
      createSheet(workbook, "PAUSED - Schedule Rules", 2, 
          textAdsStructureOutOfSchedule, AdGroupAdStatus.ENABLED);

      createSheet(workbook, "IN-STOCK NOT Touched", 3, 
          textAdsStructureInStock, AdGroupAdStatus.ENABLED);

      createSheet(workbook, "OUT-OF-STOCK NOT Touched", 4, 
          textAdsStructureOutOfStock, AdGroupAdStatus.PAUSED);
      
      createFailSheet(workbook, "ERRORS", 5);

      workbook.write(); 
      workbook.close(); 
    } catch (IOException e) {
      DumpExceptions.dumpIOException(e);
    } catch (WriteException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return workbook;
  }
  
  public WritableSheet createFailSheet(WritableWorkbook workbook, String title, 
      int pos) {
    WritableSheet sheet = workbook.createSheet(title, pos);
    ResultSet rs = null;
    PreparedStatement prep = null;
    try {
      
      sheet.addCell(new Label(0, 0, "Campaign"));
      sheet.addCell(new Label(1, 0, "AdGroup"));
      sheet.addCell(new Label(2, 0, "HeadLine"));
      sheet.addCell(new Label(3, 0, "Descripton1"));
      sheet.addCell(new Label(4, 0, "Descripton2"));
      sheet.addCell(new Label(5, 0, "DisplayUrl"));
      sheet.addCell(new Label(6, 0, "Param"));
      sheet.addCell(new Label(7, 0, "Current Value"));
      sheet.addCell(new Label(8, 0, "Should Be One Of"));
      
      prep = conn.prepareStatement(
          "SELECT ca.Name Campaign, ag.Name AdGroup, ta.* " +
          "FROM TextAds ta, Campaigns ca, AdGroups ag " +
          "WHERE ta.LocalAdGroupId = ag.LocalId " + 
          "AND ag.LocalCampaignId = ca.LocalId " + 
          "AND (" +
            "DestinationURL like '%aw_catid%' " +
            "OR DestinationURL like '%aw_prodid%')");
      rs = prep.executeQuery();
      URLParams urlParams = new URLParams();
      int line = 1;
      while (rs.next()) {
        String campaign = rs.getString("Campaign");
        String adGroup = rs.getString("AdGroup");
        TextAdStructure textAdStructure = textAdTable.getStructure(rs);
        urlParams.extract(new URL(textAdStructure.getDestinationUrl()));
        
        String validsAdParamProdId[] = {"PRICE", "PARCEL", "PARCELVALUE", 
            "UNITS", "DISCOUNT"};
        String validsAdParamCatId[] = {"LOWERPRICE", "MAXDISCOUNT", "ITEMS", 
            "LOWERPARCEL", "LOWERPARCELPRICE"};
        
        if (urlParams.prodId.length() != 0) {
          if (urlParams.adParam1.length() != 0) {
            if (!ArrayUtils.contains(validsAdParamProdId, 
                urlParams.adParam1.toUpperCase())) {
              sheet.addCell(new Label(0, line, campaign));
              sheet.addCell(new Label(1, line, adGroup));
              sheet.addCell(new Label(2, line, textAdStructure.getHeadline()));
              sheet.addCell(new Label(3, line, textAdStructure.getDescription1()));
              sheet.addCell(new Label(4, line, textAdStructure.getDescription2()));
              sheet.addCell(new Label(5, line, textAdStructure.getDisplayUrl()));
              sheet.addCell(new Label(6, line, "AdParam1"));
              sheet.addCell(new Label(7, line, urlParams.adParam1));
              sheet.addCell(new Label(8, line, Utils.toString(validsAdParamProdId)));              
              line++;              
            }
          }
          if (urlParams.adParam2.length() != 0) {
            if (!ArrayUtils.contains(validsAdParamProdId, 
                urlParams.adParam2.toUpperCase())) {
              sheet.addCell(new Label(0, line, campaign));
              sheet.addCell(new Label(1, line, adGroup));
              sheet.addCell(new Label(2, line, textAdStructure.getHeadline()));
              sheet.addCell(new Label(3, line, textAdStructure.getDescription1()));
              sheet.addCell(new Label(4, line, textAdStructure.getDescription2()));
              sheet.addCell(new Label(5, line, textAdStructure.getDisplayUrl()));
              sheet.addCell(new Label(6, line, "AdParam2"));
              sheet.addCell(new Label(7, line, urlParams.adParam2));
              sheet.addCell(new Label(8, line, Utils.toString(validsAdParamProdId)));              
              line++;              
            }
          }
        }
        if (urlParams.catId.length() != 0) {
          if (urlParams.adParam1.length() != 0) {
            if (!ArrayUtils.contains(validsAdParamCatId, 
                urlParams.adParam1.toUpperCase())) {
              sheet.addCell(new Label(0, line, campaign));
              sheet.addCell(new Label(1, line, adGroup));
              sheet.addCell(new Label(2, line, textAdStructure.getHeadline()));
              sheet.addCell(new Label(3, line, textAdStructure.getDescription1()));
              sheet.addCell(new Label(4, line, textAdStructure.getDescription2()));
              sheet.addCell(new Label(5, line, textAdStructure.getDisplayUrl()));
              sheet.addCell(new Label(6, line, "AdParam1"));
              sheet.addCell(new Label(7, line, urlParams.adParam1));
              sheet.addCell(new Label(8, line, Utils.toString(validsAdParamCatId)));              
              line++;              
            }
          }
          if (urlParams.adParam2.length() != 0) {
            if (!ArrayUtils.contains(validsAdParamCatId, 
                urlParams.adParam2.toUpperCase())) {
              sheet.addCell(new Label(0, line, campaign));
              sheet.addCell(new Label(1, line, adGroup));
              sheet.addCell(new Label(2, line, textAdStructure.getHeadline()));
              sheet.addCell(new Label(3, line, textAdStructure.getDescription1()));
              sheet.addCell(new Label(4, line, textAdStructure.getDescription2()));
              sheet.addCell(new Label(5, line, textAdStructure.getDisplayUrl()));
              sheet.addCell(new Label(6, line, "AdParam2"));
              sheet.addCell(new Label(7, line, urlParams.adParam2));
              sheet.addCell(new Label(8, line, Utils.toString(validsAdParamCatId)));              
              line++;              
            }
          }
        }        
      }
      
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } catch (MalformedURLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (RowsExceededException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (WriteException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return sheet;
  }
  
  public WritableSheet createSheet(WritableWorkbook workbook, String title, 
      int pos, HashSet<TextAdStructure> textAds, AdGroupAdStatus filter) {
    WritableSheet sheet = workbook.createSheet(title, pos);

    try {
      sheet.addCell(new Label(0, 0, "Campaign"));
      sheet.addCell(new Label(1, 0, "AdGroup"));
      sheet.addCell(new Label(2, 0, "HeadLine"));
      sheet.addCell(new Label(3, 0, "Descripton1"));
      sheet.addCell(new Label(4, 0, "Descripton2"));
      sheet.addCell(new Label(5, 0, "DisplayUrl"));
      sheet.addCell(new Label(6, 0, "ProductId"));
      sheet.addCell(new Label(7, 0, "CategoryId"));
      
      int line = 1;
      Iterator<TextAdStructure> i = textAds.iterator();
      while (i.hasNext()) {
        TextAdStructure textAdStructure = i.next();
    
        if (textAdStructure.getStatus().getValue() == filter.getValue()) {      
          AdGroupStructure adGroupStructure 
              = adGroupTable.get(textAdStructure.localAdGroupId);
          CampaignStructure campaignStructure 
              = campaignTable.get(adGroupStructure.getLocalCampaignId());
    
          String productId 
              = textAd2ProdId.getProductId(textAdStructure.getLocalId());
          String categoryId 
              = textAd2CatId.getCategoryId(textAdStructure.getLocalId());
    
          sheet.addCell(new Label(0, line, campaignStructure.getName()));
          sheet.addCell(new Label(1, line, adGroupStructure.getName()));
          sheet.addCell(new Label(2, line, textAdStructure.getHeadline()));
          sheet.addCell(new Label(3, line, textAdStructure.getDescription1()));
          sheet.addCell(new Label(4, line, textAdStructure.getDescription2()));
          sheet.addCell(new Label(5, line, textAdStructure.getDisplayUrl()));
          sheet.addCell(new Label(6, line, productId));
          sheet.addCell(new Label(7, line, categoryId));
          line++;
        }
      }
      
    } catch (RowsExceededException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (WriteException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    
    return sheet;
  }

}
